﻿CREATE PROC sacm.spObtenDiasAgenda
	@pnIdEmpresa	INT,
	@pnIdSucursal	INT,
	@pnIdDoctor		INT
AS
BEGIN
	SET NOCOUNT OFF;

	DECLARE @sYear CHAR(4),
			@sMonth CHAR(2),
			@tHoy	DATE,
			@nMeses INT
	DECLARE @tblDummyFechas TABLE (Fecha DATE, IdDiaSemana INT)

	SELECT	@tHoy = GETDATE(), @nMeses = 2
	SELECT	@sYear = CAST(YEAR(@tHoy) AS CHAR(4)),
			@sMonth= RIGHT('00' + CAST(MONTH(@tHoy) AS VARCHAR(2)), 2)
			
	-- Obtengo los dias Disponibles
	;WITH 
	 N0 as (SELECT 1 as n UNION ALL SELECT 1)
	,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
	,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
	,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
	,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
	,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
	,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
	,Nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)

	INSERT INTO @tblDummyFechas
	        ( Fecha, IdDiaSemana )
	SELECT	DATEADD(day,num-1,@tHoy) as Fecha, DATEPART(weekday, DATEADD(day,num-1,@tHoy)) IdDiaSemana
	FROM	Nums
	WHERE	num <= DATEDIFF(day,@tHoy,DATEADD(MONTH, @nMeses, @tHoy)) + 1
	
	SET LANGUAGE us_english	-- Para que el dia de semana sea 1 - Domingo	
	
	SELECT	f.Fecha, f.IdDiaSemana, CASE WHEN e.IdAgendaDiaExcluir IS NULL THEN 0 ELSE 1 END AS DiaExcluir
	FROM	@tblDummyFechas		f
	INNER JOIN sacm.AgendaDet	d
	ON		f.IdDiaSemana = d.IdDiaSemana
	INNER JOIN sacm.Agenda		a
	ON		d.IdAgenda = a.IdAgenda AND d.IdEmpresa = a.IdEmpresa AND d.IdSucursal = a.IdSucursal
	LEFT JOIN sacm.AgendaDiaExcluir 	e
	ON		e.EsDiaCompleto	= 1
	AND		e.FechaExcluir	= f.Fecha
	WHERE	d.IdEmpresa	= @pnIdEmpresa
	AND		d.IdSucursal= @pnIdSucursal
	AND		a.IdDoctor	= @pnIdDoctor
	ORDER BY f.Fecha			

	SELECT	f.Fecha, f.IdDiaSemana,d.HoraIni, d.HoraFin,
		-- Horas trabajadas
		CASE WHEN d.HoraBreakIni IS NULL THEN
			DATEDIFF(minute,d.HoraIni, d.HoraFin)/60.0
		ELSE 
			(DATEDIFF(minute,d.HoraIni, d.HoraFin) - DATEDIFF(minute,d.HoraBreakIni, d.HoraBreakFin))/60.0 END AS Horas,
		-- Citas a trabajar
		CASE WHEN d.HoraBreakIni IS NULL THEN
			DATEDIFF(minute,d.HoraIni, d.HoraFin)
		ELSE 
			(DATEDIFF(minute,d.HoraIni, d.HoraFin) - DATEDIFF(minute,d.HoraBreakIni, d.HoraBreakFin)) END
		/ r.ValorMin	AS NumCitasDisp,
		-- Citas programadas
		COUNT(s.IdEstatusSolicitudCita) AS NumCitasAct,
		--
		CASE WHEN COUNT(s.IdEstatusSolicitudCita) <= 2 THEN 1 ELSE 0 END AS AltaDisp,
		
		CASE WHEN COUNT(s.IdEstatusSolicitudCita)
			BETWEEN 3 
				AND (	CASE WHEN d.HoraBreakIni IS NULL THEN
							DATEDIFF(minute,d.HoraIni, d.HoraFin)
						ELSE 
							(DATEDIFF(minute,d.HoraIni, d.HoraFin) - DATEDIFF(minute,d.HoraBreakIni, d.HoraBreakFin)) END
						/ r.ValorMin)/2
			THEN 1 ELSE 0 END AS MedianaDisp,
		
		CASE WHEN COUNT(s.IdEstatusSolicitudCita) BETWEEN
				(
					(	CASE WHEN d.HoraBreakIni IS NULL THEN
							DATEDIFF(minute,d.HoraIni, d.HoraFin)
						ELSE 
							(DATEDIFF(minute,d.HoraIni, d.HoraFin) - DATEDIFF(minute,d.HoraBreakIni, d.HoraBreakFin)) END
						/ r.ValorMin)/2
				)
				AND
				(	CASE WHEN d.HoraBreakIni IS NULL THEN
							DATEDIFF(minute,d.HoraIni, d.HoraFin)
						ELSE 
							(DATEDIFF(minute,d.HoraIni, d.HoraFin) - DATEDIFF(minute,d.HoraBreakIni, d.HoraBreakFin)) END
						/ r.ValorMin) - 1
			THEN 1 ELSE 0 END AS BajaDisp,
		 CASE WHEN COUNT(s.IdEstatusSolicitudCita) >=
				(	CASE WHEN d.HoraBreakIni IS NULL THEN
							DATEDIFF(minute,d.HoraIni, d.HoraFin)
						ELSE 
							(DATEDIFF(minute,d.HoraIni, d.HoraFin) - DATEDIFF(minute,d.HoraBreakIni, d.HoraBreakFin)) END
						/ r.ValorMin) THEN 1 ELSE 0 END SinDisp
	FROM	@tblDummyFechas			f
	INNER JOIN sacm.AgendaDet		d
	ON		f.IdDiaSemana = d.IdDiaSemana
	INNER JOIN sacm.Agenda			a
	ON		d.IdAgenda = a.IdAgenda AND d.IdEmpresa = a.IdEmpresa AND d.IdSucursal = a.IdSucursal
	INNER JOIN sacm.CatRangoMinCita	r
	ON		a.IdRangoMinCita = r.IdRangoMinCita
	LEFT JOIN sacm.SolicitudCita	s
	ON		s.IdDoctor	= a.IdDoctor
	AND		s.IdEstatusSolicitudCita IN (1,2) -- Capturada o Confirmada
	AND		f.Fecha		= s.FechaSolicitudCita
	LEFT JOIN (
		SELECT	f.Fecha, f.IdDiaSemana, CASE WHEN e.IdAgendaDiaExcluir IS NULL THEN 0 ELSE 1 END AS DiaExcluir
		FROM	@tblDummyFechas		f
		INNER JOIN sacm.AgendaDet	d
		ON		f.IdDiaSemana = d.IdDiaSemana
		INNER JOIN sacm.Agenda		a
		ON		d.IdAgenda = a.IdAgenda AND d.IdEmpresa = a.IdEmpresa AND d.IdSucursal = a.IdSucursal
		LEFT JOIN sacm.AgendaDiaExcluir 	e
		ON		e.EsDiaCompleto	= 1
		AND		e.FechaExcluir	= f.Fecha
		WHERE	d.IdEmpresa	= @pnIdEmpresa
		AND		d.IdSucursal= @pnIdSucursal
		AND		a.IdDoctor	= @pnIdDoctor
	)	ex
	ON	ex.Fecha = f.Fecha
	AND	ex.DiaExcluir = 1
	WHERE	d.IdEmpresa	= @pnIdEmpresa
	AND		d.IdSucursal= @pnIdSucursal
	AND		a.IdDoctor	= @pnIdDoctor
	AND		ex.Fecha	IS NULL
	GROUP BY f.Fecha, f.IdDiaSemana,d.HoraIni, d.HoraFin, d.HoraBreakIni, d.HoraBreakFin, r.ValorMin
	ORDER BY f.Fecha			
		
	SET NOCOUNT ON;
END